<!DOCTYPE html>
<html>
  <head>
  <meta http-equiv="content-type" content="text/html; charset=utf-8">
  <meta content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=0" name="viewport">
  <meta name="description" content="刘清政">
  <meta name="keyword" content="hexo-theme">
  
    <link rel="shortcut icon" href="/css/images/logo.png">
  
  <title>
    
      db/MySQL系列/03-MySQL系列之-SQL基础应用 | Justin-刘清政的博客
    
  </title>
  <link href="//cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet">
  <link href="//cdnjs.cloudflare.com/ajax/libs/nprogress/0.2.0/nprogress.min.css" rel="stylesheet">
  <link href="//cdnjs.cloudflare.com/ajax/libs/highlight.js/9.12.0/styles/tomorrow.min.css" rel="stylesheet">
  
<link rel="stylesheet" href="/css/style.css">

  
    
<link rel="stylesheet" href="/css/plugins/gitment.css">

  
  <script src="//cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
  <script src="//cdnjs.cloudflare.com/ajax/libs/geopattern/1.2.3/js/geopattern.min.js"></script>
  <script src="//cdnjs.cloudflare.com/ajax/libs/nprogress/0.2.0/nprogress.min.js"></script>
  
    
<script src="/js/qrious.js"></script>

  
  
    
<script src="/js/gitment.js"></script>

  
  

  
<meta name="generator" content="Hexo 4.2.0"></head>
<div class="wechat-share">
  <img src="/css/images/logo.png" />
</div>

  <body>
    <header class="header fixed-header">
  <div class="header-container">
    <a class="home-link" href="/">
      <div class="logo"></div>
      <span>Justin-刘清政的博客</span>
    </a>
    <ul class="right-list">
      
        <li class="list-item">
          
            <a href="/" class="item-link">主页</a>
          
        </li>
      
        <li class="list-item">
          
            <a href="/tags/" class="item-link">标签</a>
          
        </li>
      
        <li class="list-item">
          
            <a href="/archives/" class="item-link">归档</a>
          
        </li>
      
        <li class="list-item">
          
            <a href="/about/" class="item-link">关于我</a>
          
        </li>
      
    </ul>
    <div class="menu">
      <span class="icon-bar"></span>
      <span class="icon-bar"></span>
      <span class="icon-bar"></span>
    </div>
    <div class="menu-mask">
      <ul class="menu-list">
        
          <li class="menu-item">
            
              <a href="/" class="menu-link">主页</a>
            
          </li>
        
          <li class="menu-item">
            
              <a href="/tags/" class="menu-link">标签</a>
            
          </li>
        
          <li class="menu-item">
            
              <a href="/archives/" class="menu-link">归档</a>
            
          </li>
        
          <li class="menu-item">
            
              <a href="/about/" class="menu-link">关于我</a>
            
          </li>
        
      </ul>
    </div>
  </div>
</header>

    <div id="article-banner">
  <h2>db/MySQL系列/03-MySQL系列之-SQL基础应用</h2>



  <p class="post-date">2019-12-24</p>
    <!-- 不蒜子统计 -->
    <span id="busuanzi_container_page_pv" style='display:none' class="">
        <i class="icon-smile icon"></i> 阅读数：<span id="busuanzi_value_page_pv"></span>次
    </span>
  <div class="arrow-down">
    <a href="javascript:;"></a>
  </div>
</div>
<main class="app-body flex-box">
  <!-- Article START -->
  <article class="post-article">
    <section class="markdown-content"><h1 id="一-SQL介绍"><a href="#一-SQL介绍" class="headerlink" title="一 SQL介绍"></a>一 SQL介绍</h1><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">结构化查询语言</span><br><span class="line">有一些标准：<span class="number">89</span> <span class="number">92</span> <span class="number">99</span> <span class="number">03</span></span><br><span class="line"><span class="number">5.7</span> 以后符合SQL92严格模式</span><br><span class="line">通过sql_mode参数来控制</span><br><span class="line"></span><br><span class="line"><span class="comment"># 查看sql_mode，sql_mode用来规范sql语句的书写方式</span></span><br><span class="line">select @@sql_mode;  <span class="comment"># 查看sql_mode</span></span><br><span class="line">ONLY_FULL_GROUP_BY, <span class="comment"># 5.7新加入</span></span><br><span class="line">STRICT_TRANS_TABLES,</span><br><span class="line">NO_ZERO_IN_DATE,</span><br><span class="line">NO_ZERO_DATE,</span><br><span class="line">ERROR_FOR_DIVISION_BY_ZERO,</span><br><span class="line">NO_AUTO_CREATE_USER,</span><br><span class="line">NO_ENGINE_SUBSTITUTION</span><br></pre></td></tr></table></figure>

<h1 id="二-常用SQL分类"><a href="#二-常用SQL分类" class="headerlink" title="二 常用SQL分类"></a>二 常用SQL分类</h1><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># help:客户端功能帮助</span></span><br><span class="line"><span class="comment"># help contents：服务端功能帮助</span></span><br><span class="line"><span class="comment"># help Data Definition</span></span><br><span class="line"><span class="comment"># help DROP DATABASE</span></span><br><span class="line"></span><br><span class="line">DDL：数据定义语言</span><br><span class="line">DCL：数据控制语言</span><br><span class="line">DML：数据操作语言</span><br><span class="line">DQL：数据的查询语言</span><br></pre></td></tr></table></figure>

<h3 id="2-1-客户端命令"><a href="#2-1-客户端命令" class="headerlink" title="2.1 客户端命令"></a>2.1 客户端命令</h3><img src="https://tva1.sinaimg.cn/large/007S8ZIlgy1gicgbnw0e7j31ek0qynlo.jpg" alt="image-20200813150640462" style="zoom:50%;" />

<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 客户端输入help</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># \c  结束上一条命令</span></span><br><span class="line"><span class="comment"># \G  格式化输出</span></span><br><span class="line"><span class="comment"># exit，\q,control+d，quit退出会话</span></span><br><span class="line"><span class="comment"># notee和tee  开启日志记录，</span></span><br><span class="line">tee/tmp/mysql.log <span class="comment"># 开启，以后执行的sql都会被记录到日志，包括结果</span></span><br><span class="line">select * <span class="keyword">from</span> t2；</span><br><span class="line"><span class="comment"># source  导入sql脚本，类似于&lt;,恢复备份</span></span><br><span class="line">source /root/my.sql</span><br><span class="line"><span class="comment"># system 在mysql中执行linux命令</span></span><br><span class="line">system ls</span><br><span class="line">system cd /tmp &amp;&amp; ls</span><br></pre></td></tr></table></figure>



<h1 id="三-数据类型、表属性、字符集"><a href="#三-数据类型、表属性、字符集" class="headerlink" title="三 数据类型、表属性、字符集"></a>三 数据类型、表属性、字符集</h1><h2 id="3-1-数据类型"><a href="#3-1-数据类型" class="headerlink" title="3.1  数据类型"></a>3.1  数据类型</h2><h3 id="3-1-1-作用"><a href="#3-1-1-作用" class="headerlink" title="3.1.1 作用"></a>3.1.1 作用</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">保证数据的准确性和标准性。</span><br></pre></td></tr></table></figure>

<h3 id="3-1-2-种类"><a href="#3-1-2-种类" class="headerlink" title="3.1.2 种类"></a>3.1.2 种类</h3><p><strong>数值类型</strong></p>
<img src="https://tva1.sinaimg.cn/large/007S8ZIlgy1gicgbs2qcdj30z80nyaof.jpg" alt="image-20200813115647750" style="zoom:50%;" />

<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">tinyint  ： <span class="number">-128</span>~<span class="number">127</span>     <span class="number">1</span>个字节，<span class="number">8</span>个比特位，正负<span class="number">2</span>的<span class="number">7</span>次方减<span class="number">1</span></span><br><span class="line">int      ：<span class="number">-2</span>^<span class="number">31</span>~<span class="number">2</span>^<span class="number">31</span><span class="number">-1</span>  <span class="number">4</span>个字节，<span class="number">32</span>个比特位，正负<span class="number">2</span>的<span class="number">31</span>次方减<span class="number">1</span></span><br><span class="line">bigint   : <span class="number">-2</span>^<span class="number">63</span>~<span class="number">2</span>^<span class="number">63</span><span class="number">-1</span>  <span class="number">8</span>个字节，<span class="number">64</span>个比特位，正负<span class="number">2</span>的<span class="number">63</span>次方减<span class="number">1</span></span><br><span class="line">说明：手机号是无法存储到int的。一般是使用char类型来存储收集号</span><br><span class="line">  </span><br><span class="line"><span class="comment"># 创建表指定数据类型</span></span><br><span class="line">create tabel t1(id int,name varchar(<span class="number">64</span>),age tinyint)</span><br></pre></td></tr></table></figure>

<p><strong>字符类型</strong></p>
<img src="https://tva1.sinaimg.cn/large/007S8ZIlgy1gicgbvqwh2j30xq0ngqgc.jpg" alt="image-20200813115855936" style="zoom:50%;" />

<p>image</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br></pre></td><td class="code"><pre><span class="line">char(<span class="number">11</span>) ：</span><br><span class="line">定长 的字符串类型,在存储字符串时，最大字符长度<span class="number">11</span>个，立即分配<span class="number">11</span>个字符长度的存储空间，如果存不满，空格填充。</span><br><span class="line">varchar(<span class="number">11</span>):</span><br><span class="line">变长 的字符串类型看，最大字符长度<span class="number">11</span>个。在存储字符串时，自动判断字符长度，按需分配存储空间。</span><br><span class="line">varchar类型，除了会存储字符串之外，还会额外使用<span class="number">1</span><span class="number">-2</span>个字节存储字符长度</span><br><span class="line">enum(<span class="string">'bj'</span>,<span class="string">'tj'</span>,<span class="string">'sh'</span>)：</span><br><span class="line">枚举类型，存字符串类型，比较适合于将来此列的值是固定范围内的特点，可以使用enum,可以很大程度的优化我们的索引结构。</span><br><span class="line">  </span><br><span class="line">  </span><br><span class="line"><span class="comment"># 括号中数字指的是字符长度，所以存英文和中文，其实占得空间是不一样的</span></span><br><span class="line"><span class="comment"># 英文和数字，一个字符是1个字节，中文3个字节,emoji占4个字节</span></span><br><span class="line"><span class="comment"># 因为编码方式规定了utf8，所以不需要自行考虑此问题</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># 测试：</span></span><br><span class="line">create database db1 charset utf8mb4;</span><br><span class="line">create table t2(id int,name varchar(<span class="number">10</span>),sex char(<span class="number">10</span>));</span><br><span class="line">insert into t2 values(<span class="number">1</span>,<span class="string">'aaaaaaaaaa'</span>,<span class="string">'一二三四五六七八九十'</span>);</span><br><span class="line">insert into t2 values(<span class="number">1</span>,<span class="string">'aaaaaaaaaa'</span>,<span class="string">'一二三四五六七八九十一'</span>);</span><br><span class="line">insert into t2 values(<span class="number">1</span>,<span class="string">'aaaaaaaaaa'</span>,<span class="string">'1234567891'</span>);</span><br><span class="line">ERROR <span class="number">1406</span> (<span class="number">22001</span>): Data too long <span class="keyword">for</span> column <span class="string">'sex'</span> at row <span class="number">1</span></span><br><span class="line">desc t2；<span class="comment"># 查看表结构</span></span><br><span class="line">select length(sex) <span class="keyword">from</span> t2; <span class="comment"># 查看字符所占用的空间</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># mysql 5.6 超长会存进去，自动截断</span></span><br><span class="line"><span class="comment"># mysql 5.6 超长会报错</span></span><br><span class="line"></span><br><span class="line">varchar最多能存储<span class="number">65535</span>个字节的数据，一般我们最多定义varchar(<span class="number">255</span>)，超过<span class="number">255</span>会被转成text类型</span><br></pre></td></tr></table></figure>

<p><strong>时间类型</strong></p>
<img src="https://tva1.sinaimg.cn/large/007S8ZIlgy1gicgc00w2hj30zy0jqdqz.jpg" alt="image-20200813115913262" style="zoom:50%;" />




<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">DATETIME （<span class="number">8</span>个字节长度）</span><br><span class="line">范围为从 <span class="number">1000</span><span class="number">-01</span><span class="number">-01</span> <span class="number">00</span>:<span class="number">00</span>:<span class="number">00.000000</span> 至 <span class="number">9999</span><span class="number">-12</span><span class="number">-31</span> <span class="number">23</span>:<span class="number">59</span>:<span class="number">59.999999</span>。</span><br><span class="line">TIMESTAMP （<span class="number">4</span>个字节长度）</span><br><span class="line"><span class="number">1970</span><span class="number">-01</span><span class="number">-01</span> <span class="number">00</span>:<span class="number">00</span>:<span class="number">00.000000</span> 至 <span class="number">2038</span><span class="number">-01</span><span class="number">-19</span> <span class="number">03</span>:<span class="number">14</span>:<span class="number">07.999999</span>。</span><br><span class="line">timestamp会受到时区的影响</span><br></pre></td></tr></table></figure>

<p><strong>二进制类型</strong></p>
<img src="https://tva1.sinaimg.cn/large/007S8ZIlgy1gicgc3u5zrj30ze0msqhl.jpg" alt="image-20200813115928772" style="zoom:50%;" />

<p><strong>json格式</strong></p>
<p>5.6以后支持</p>
<h2 id="3-2-表属性"><a href="#3-2-表属性" class="headerlink" title="3.2 表属性"></a>3.2 表属性</h2><h3 id="3-2-1-列属性"><a href="#3-2-1-列属性" class="headerlink" title="3.2.1 列属性"></a>3.2.1 列属性</h3><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br></pre></td><td class="code"><pre><span class="line">约束(一般建表时添加):</span><br><span class="line"><span class="comment"># primary key ：主键约束，</span></span><br><span class="line">唯一且非空，每个表只能有一个主键，作为聚簇索引</span><br><span class="line">设置为主键的列，此列的值必须非空且唯一，主键在一个表中只能有一个，但是可以有多个列一起构成。</span><br><span class="line">  </span><br><span class="line"><span class="comment">#not null ：非空约束</span></span><br><span class="line">列值不能为空，也是表设计的规范，尽可能将所有的列设置为非空。可以设置默认值为<span class="number">0</span></span><br><span class="line">  </span><br><span class="line"><span class="comment"># unique key：唯一键</span></span><br><span class="line">列值不能重复</span><br><span class="line">  </span><br><span class="line"><span class="comment"># unsigned：无符号</span></span><br><span class="line">针对数字列，非负数。 </span><br><span class="line"></span><br><span class="line">其他属性:</span><br><span class="line"><span class="comment"># key:索引</span></span><br><span class="line">可以在某列上建立索引，来优化查询,一般是根据需要后添加</span><br><span class="line"><span class="comment"># default :默认值</span></span><br><span class="line">列中，没有录入值时，会自动使用default的值填充</span><br><span class="line"><span class="comment"># auto_increment :自增长</span></span><br><span class="line">针对数字列，顺序的自动填充数据（默认是从<span class="number">1</span>开始，将来可以设定起始点和偏移量）</span><br><span class="line"><span class="comment"># comment   : 注释</span></span><br></pre></td></tr></table></figure>

<h3 id="3-2-2-表的属性"><a href="#3-2-2-表的属性" class="headerlink" title="3.2.2 表的属性"></a>3.2.2 表的属性</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">存储引擎:</span><br><span class="line">InnoDB（默认的）</span><br><span class="line">字符集和排序规则:</span><br><span class="line">utf8       </span><br><span class="line">utf8mb4</span><br></pre></td></tr></table></figure>

<h2 id="3-3-字符集和校对规则"><a href="#3-3-字符集和校对规则" class="headerlink" title="3.3 字符集和校对规则"></a>3.3 字符集和校对规则</h2><h3 id="3-3-1-字符集（charset）"><a href="#3-3-1-字符集（charset）" class="headerlink" title="3.3.1 字符集（charset）"></a>3.3.1 字符集（charset）</h3><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># show charset;</span></span><br><span class="line">有非常多，现在只关注如下两种</span><br><span class="line">utf8       </span><br><span class="line">utf8mb4  <span class="comment"># 5.6以后出现，8.0以后默认使用utf8mb4，8.0以前默认是latin1（拉丁）</span></span><br><span class="line"><span class="comment"># 差别：</span></span><br><span class="line">utf8：最大存储长度，单个字符最多<span class="number">3</span>个字节</span><br><span class="line">utf8mb4支持的编码比utf8更多，比如emoji字符，emoji字符，一个字符占<span class="number">4</span>个字节</span><br><span class="line"></span><br><span class="line"><span class="comment"># 查看库的字符编码</span></span><br><span class="line">show create databaes mysql</span><br></pre></td></tr></table></figure>

<h3 id="3-3-2-校对规则（排序规则，collation）"><a href="#3-3-2-校对规则（排序规则，collation）" class="headerlink" title="3.3.2 校对规则（排序规则，collation）"></a>3.3.2 校对规则（排序规则，collation）</h3><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line">show collation;</span><br><span class="line"><span class="comment"># 影响排序规则</span></span><br><span class="line">a</span><br><span class="line">b</span><br><span class="line">A</span><br><span class="line">aB</span><br><span class="line">Ba</span><br><span class="line">select ascii(<span class="string">'a'</span>); <span class="comment"># 查看a的ascii码</span></span><br><span class="line">如果大小写敏感排序一个样</span><br><span class="line">如果大小写敏感排序另一个样</span><br><span class="line"></span><br><span class="line"><span class="comment"># 简单来说就是：大小写是否敏感，默认不敏感</span></span><br><span class="line">+--------------------------+----------+-----+---------+----------+---------+</span><br><span class="line">| Collation                | Charset  | Id  | Default | Compiled | Sortlen |</span><br><span class="line">+--------------------------+----------+-----+---------+----------+---------+</span><br><span class="line">| utf8mb4_general_ci       | utf8mb4  |  <span class="number">45</span> | Yes     | Yes      |       <span class="number">1</span> |</span><br><span class="line">| utf8mb4_bin              | utf8mb4  |  <span class="number">46</span> |         | Yes      |       <span class="number">1</span> |</span><br></pre></td></tr></table></figure>

<h1 id="四-DDL应用"><a href="#四-DDL应用" class="headerlink" title="四 DDL应用"></a>四 DDL应用</h1><h2 id="4-1-数据定义语言"><a href="#4-1-数据定义语言" class="headerlink" title="4.1 数据定义语言"></a>4.1 数据定义语言</h2><p>数据定义语言，对库和表进行操作，操作mysql的对象，即库和表，对元数据进行操作</p>
<h2 id="4-2-库定义"><a href="#4-2-库定义" class="headerlink" title="4.2 库定义"></a>4.2 库定义</h2><h3 id="4-2-1-创建"><a href="#4-2-1-创建" class="headerlink" title="4.2.1 创建"></a>4.2.1 创建</h3><h3 id="4-2-1-创建数据库"><a href="#4-2-1-创建数据库" class="headerlink" title="4.2.1 创建数据库"></a>4.2.1 创建数据库</h3><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">create database school;</span><br><span class="line">create schema sch;</span><br><span class="line">show charset; <span class="comment"># 查看支持的字符集</span></span><br><span class="line">show collation; <span class="comment"># 查看支持的校对规则，collation</span></span><br><span class="line">CREATE DATABASE test CHARSET utf8;</span><br><span class="line">create database xyz charset utf8mb4 collate utf8mb4_bin;</span><br><span class="line"></span><br><span class="line">建库规范：</span><br><span class="line"><span class="number">1.</span>库名不能有大写字母   </span><br><span class="line"><span class="number">2.</span>建库要加字符集         </span><br><span class="line"><span class="number">3.</span>库名不能有数字开头，不能使用保留字段（database，table）</span><br><span class="line"><span class="number">4.</span>库名要和业务相关</span><br></pre></td></tr></table></figure>

<p>建库标准语句</p>
<figure class="highlight dart"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; create database db charset utf8mb4;</span><br><span class="line">mysql&gt; <span class="keyword">show</span> create database xuexiao;</span><br></pre></td></tr></table></figure>

<h3 id="4-2-2-删除-生产中禁止使用"><a href="#4-2-2-删除-生产中禁止使用" class="headerlink" title="4.2.2 删除(生产中禁止使用)"></a>4.2.2 删除(生产中禁止使用)</h3><figure class="highlight rust"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; <span class="built_in">drop</span> database lqz;</span><br></pre></td></tr></table></figure>

<h3 id="4-2-3-修改"><a href="#4-2-3-修改" class="headerlink" title="4.2.3 修改"></a>4.2.3 修改</h3><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">SHOW CREATE DATABASE school;</span><br><span class="line">ALTER DATABASE school  CHARSET utf8;</span><br><span class="line"><span class="comment"># 注意：</span></span><br><span class="line">只能改库属性，不能改库名（只能改字符集）</span><br><span class="line">修改字符集，修改后的字符集一定是原字符集的严格超集</span><br><span class="line">从小往大改，从utf8改到utf8mb4可以，从utf8mb4改成utf8可能会乱码</span><br></pre></td></tr></table></figure>

<h3 id="4-2-4-查询库相关信息（DQL）"><a href="#4-2-4-查询库相关信息（DQL）" class="headerlink" title="4.2.4 查询库相关信息（DQL）"></a>4.2.4 查询库相关信息（DQL）</h3><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">show databases； <span class="comment"># 查看库</span></span><br><span class="line">show create database lqz； <span class="comment"># 查看具体信息</span></span><br></pre></td></tr></table></figure>

<h2 id="4-3-表定义"><a href="#4-3-表定义" class="headerlink" title="4.3 表定义"></a>4.3 表定义</h2><h3 id="4-3-1-创建"><a href="#4-3-1-创建" class="headerlink" title="4.3.1 创建"></a>4.3.1 创建</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">create table stu(</span><br><span class="line">列1  属性（数据类型、约束、其他属性） ，</span><br><span class="line">列2  属性，</span><br><span class="line">列3  属性</span><br><span class="line">)</span><br></pre></td></tr></table></figure>

<h3 id="4-3-2-建表"><a href="#4-3-2-建表" class="headerlink" title="4.3.2 建表"></a>4.3.2 建表</h3><figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">USE</span> <span class="title">school</span>;</span><br><span class="line">CREATE TABLE stu(</span><br><span class="line">id      INT NOT <span class="keyword">NULL</span> PRIMARY KEY AUTO_INCREMENT COMMENT <span class="string">'学号'</span>,</span><br><span class="line">sname   VARCHAR(<span class="number">255</span>) NOT <span class="keyword">NULL</span> COMMENT <span class="string">'姓名'</span>,</span><br><span class="line">sage    TINYINT UNSIGNED NOT <span class="keyword">NULL</span> <span class="keyword">DEFAULT</span> <span class="number">0</span> COMMENT <span class="string">'年龄'</span>,</span><br><span class="line">sgender ENUM(<span class="string">'m'</span>,<span class="string">'f'</span>,<span class="string">'n'</span>) NOT <span class="keyword">NULL</span> <span class="keyword">DEFAULT</span> <span class="string">'n'</span> COMMENT <span class="string">'性别'</span> ,</span><br><span class="line">sfz     CHAR(<span class="number">18</span>) NOT <span class="keyword">NULL</span> UNIQUE  COMMENT <span class="string">'身份证'</span>,</span><br><span class="line">intime  TIMESTAMP NOT <span class="keyword">NULL</span> <span class="keyword">DEFAULT</span> NOW() COMMENT <span class="string">'入学时间'</span></span><br><span class="line">) ENGINE=INNODB CHARSET=utf8 COMMENT <span class="string">'学生表'</span>;</span><br></pre></td></tr></table></figure>

<p>建表规范：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">1. 表名小写（多平台兼容性问题，window不区分大小写，linux严格大小写），</span><br><span class="line">2. 不能是数字开头，名字不要太长15个字符以内</span><br><span class="line">3. 注意字符集和存储引擎</span><br><span class="line">4. 表名和业务有关，不能使用关键字</span><br><span class="line">5. 选择合适的数据类型：合适，简短，足够</span><br><span class="line">6. 必须有主键，每个列都要有注释</span><br><span class="line">7. 每个列设置为非空，无法保证非空，用0来填充。</span><br></pre></td></tr></table></figure>

<h3 id="4-3-2-删除-生产中禁用命令"><a href="#4-3-2-删除-生产中禁用命令" class="headerlink" title="4.3.2 删除(生产中禁用命令)"></a>4.3.2 删除(生产中禁用命令)</h3><figure class="highlight rust"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="built_in">drop</span> table t1;</span><br></pre></td></tr></table></figure>

<h3 id="4-3-3-修改"><a href="#4-3-3-修改" class="headerlink" title="4.3.3 修改"></a>4.3.3 修改</h3><ol>
<li>在stu表中添加qq列</li>
</ol>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">DESC stu;</span><br><span class="line">ALTER TABLE stu ADD qq VARCHAR(<span class="number">20</span>) NOT <span class="keyword">NULL</span> UNIQUE COMMENT <span class="string">'qq号'</span>;</span><br></pre></td></tr></table></figure>

<ol>
<li>在sname后加微信列</li>
</ol>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">ALTER TABLE stu ADD wechat VARCHAR(<span class="number">64</span>) NOT <span class="keyword">NULL</span> UNIQUE  COMMENT <span class="string">'微信号'</span> AFTER sname ;</span><br></pre></td></tr></table></figure>

<ol>
<li>在id列前加一个新列num</li>
</ol>
<figure class="highlight dart"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">ALTER TABLE stu ADD <span class="built_in">num</span> INT NOT NULL COMMENT <span class="string">'数字'</span> FIRST;</span><br><span class="line">DESC stu;</span><br></pre></td></tr></table></figure>

<ol>
<li>把刚才添加的列都删掉(危险)</li>
</ol>
<figure class="highlight dart"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">ALTER TABLE stu DROP <span class="built_in">num</span>;</span><br><span class="line">ALTER TABLE stu DROP qq;</span><br><span class="line">ALTER TABLE stu DROP wechat;</span><br></pre></td></tr></table></figure>

<ol>
<li>修改sname数据类型的属性</li>
</ol>
<figure class="highlight cpp"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="function">ALTER TABLE stu MODIFY sname <span class="title">VARCHAR</span><span class="params">(<span class="number">128</span>)</span>  NOT <span class="literal">NULL</span> </span>;</span><br></pre></td></tr></table></figure>

<ol>
<li>将sgender 改为 sg  数据类型改为 CHAR 类型（change需要把原来不需要修改的也带上）</li>
</ol>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">ALTER TABLE stu CHANGE sgender sg CHAR(<span class="number">1</span>) NOT <span class="keyword">NULL</span> <span class="keyword">DEFAULT</span> <span class="string">'n'</span> ;</span><br><span class="line">DESC stu;</span><br></pre></td></tr></table></figure>

<h4 id="注意："><a href="#注意：" class="headerlink" title="注意："></a>注意：</h4><p>在mysql中，DDL语句在对表进行操作时，是要锁“元数据表”的，此时，所有修改类的命令无法运行</p>
<p>（元数据：记录表的各种信息，对数据锁定，才能修改，否则都去改，就会出问题）</p>
<p>大表加一列，业务繁忙的表，要谨慎</p>
<p>8.0以前版本需要借助，可以借助pt-osc(pt-online-shaema-change)，gh-ost工具进行DDL操作</p>
<h3 id="4-3-4-表属性查询（DQL）"><a href="#4-3-4-表属性查询（DQL）" class="headerlink" title="4.3.4 表属性查询（DQL）"></a>4.3.4 表属性查询（DQL）</h3><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">use school</span><br><span class="line">show tables； <span class="comment"># 查看该库下所有表</span></span><br><span class="line">desc stu;     <span class="comment"># 查看表结构</span></span><br><span class="line">show create table stu；<span class="comment"># 查看详细建表语句</span></span><br><span class="line">CREATE TABLE ceshi LIKE stu;</span><br></pre></td></tr></table></figure>

<h1 id="五-DCL应用"><a href="#五-DCL应用" class="headerlink" title="五  DCL应用 ****"></a>五  DCL应用 ****</h1><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">grant </span><br><span class="line">revoke</span><br></pre></td></tr></table></figure>

<h1 id="六-DML应用"><a href="#六-DML应用" class="headerlink" title="六  DML应用"></a>六  DML应用</h1><h2 id="6-1-作用"><a href="#6-1-作用" class="headerlink" title="6.1 作用"></a>6.1 作用</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">对表中的数据行进行增、删、改</span><br></pre></td></tr></table></figure>

<h2 id="6-2-insert"><a href="#6-2-insert" class="headerlink" title="6.2 insert"></a>6.2 insert</h2><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br></pre></td><td class="code"><pre><span class="line">--- 最标准的insert语句</span><br><span class="line">INSERT INTO stu(id,sname,sage,sg,sfz,intime) </span><br><span class="line">VALUES</span><br><span class="line">(<span class="number">1</span>,<span class="string">'zs'</span>,<span class="number">18</span>,<span class="string">'m'</span>,<span class="string">'123456'</span>,NOW());</span><br><span class="line">SELECT * FROM stu;</span><br><span class="line">--- 省事的写法</span><br><span class="line">INSERT INTO stu </span><br><span class="line">VALUES</span><br><span class="line">(<span class="number">2</span>,<span class="string">'ls'</span>,<span class="number">18</span>,<span class="string">'m'</span>,<span class="string">'1234567'</span>,NOW());</span><br><span class="line">--- 针对性的录入数据</span><br><span class="line">INSERT INTO stu(sname,sfz)</span><br><span class="line">VALUES (<span class="string">'w5'</span>,<span class="string">'34445788'</span>);</span><br><span class="line">--- 同时录入多行数据</span><br><span class="line">INSERT INTO stu(sname,sfz)</span><br><span class="line">VALUES </span><br><span class="line">(<span class="string">'w55'</span>,<span class="string">'3444578d8'</span>),</span><br><span class="line">(<span class="string">'m6'</span>,<span class="string">'1212313'</span>),</span><br><span class="line">(<span class="string">'aa'</span>,<span class="string">'123213123123'</span>);</span><br><span class="line">SELECT * FROM stu;</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment"># HWM：记录自增数字，高水位线</span></span><br></pre></td></tr></table></figure>

<h2 id="6-3-update"><a href="#6-3-update" class="headerlink" title="6.3 update"></a>6.3 update</h2><figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">DESC stu;</span><br><span class="line">SELECT * FROM stu;</span><br><span class="line">UPDATE stu SET sname=<span class="string">'zhao4'</span> WHERE id=2;</span><br><span class="line">注意：update语句必须要加<span class="built_in">where</span>。</span><br></pre></td></tr></table></figure>

<h2 id="6-4-delete（危险！！）"><a href="#6-4-delete（危险！！）" class="headerlink" title="6.4 delete（危险！！）"></a>6.4 delete（危险！！）</h2><figure class="highlight objectivec"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">DELETE FROM stu  WHERE <span class="keyword">id</span>=<span class="number">3</span>;</span><br></pre></td></tr></table></figure>

<h4 id="全表删除"><a href="#全表删除" class="headerlink" title="全表删除:"></a>全表删除:</h4><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">DELETE FROM stu;</span><br><span class="line">truncate table stu;</span><br><span class="line">drop table stu;</span><br><span class="line">区别:</span><br><span class="line">delete: DML操作, 是逻辑性质删除,逐行进行删除,数据库很多，速度慢，并没有在磁盘上真正删除，磁盘空间不会立即释放，自增的值，也不会释放（HWM高水位线不会降低），</span><br><span class="line">truncate: DDL操作,物理层次删除，对与表段中的数据页进行清空,速度快，立即释放控件，HWM高水位线会降低</span><br><span class="line">drop:将表结构（元数据）和物理层次删除</span><br><span class="line"><span class="comment"># 常规方法：</span></span><br><span class="line">以上三者，都能通过备份+日志，恢复数据</span><br><span class="line"><span class="comment"># 灵活办法：</span></span><br><span class="line">delete 可以通过翻转日志（binlog）</span><br><span class="line">三种删除数据的情况，可以通过《延时从库》进行恢复</span><br></pre></td></tr></table></figure>

<h4 id="伪删除："><a href="#伪删除：" class="headerlink" title="伪删除："></a>伪删除：</h4><p>用update来替代delete，最终保证业务中查不到（select）即可</p>
<figure class="highlight objectivec"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="number">1.</span>添加状态列</span><br><span class="line">ALTER TABLE stu ADD state TINYINT NOT <span class="literal">NULL</span> DEFAULT <span class="number">1</span> ;</span><br><span class="line">SELECT * FROM stu;</span><br><span class="line"><span class="number">2.</span> UPDATE 替代 DELETE</span><br><span class="line">UPDATE stu SET state=<span class="number">0</span> WHERE <span class="keyword">id</span>=<span class="number">6</span>;</span><br><span class="line"><span class="number">3.</span> 业务语句查询</span><br><span class="line">SELECT * FROM stu WHERE state=<span class="number">1</span>;</span><br></pre></td></tr></table></figure>

<h1 id="七-DQL应用-select"><a href="#七-DQL应用-select" class="headerlink" title="七 DQL应用(select )"></a>七 DQL应用(select )</h1><h2 id="7-1-单独使用"><a href="#7-1-单独使用" class="headerlink" title="7.1 单独使用"></a>7.1 单独使用</h2><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">-- select @@xxx 查看系统参数，配置文件中配的都可以查看</span><br><span class="line">SELECT @@port;</span><br><span class="line">SELECT @@basedir;</span><br><span class="line">SELECT @@datadir;</span><br><span class="line">SELECT @@socket;</span><br><span class="line">SELECT @@server_id;</span><br><span class="line"></span><br><span class="line">show variables;  <span class="comment"># mysql中的500多个参数</span></span><br><span class="line">show variables like <span class="string">'%or%'</span>;</span><br></pre></td></tr></table></figure>

<p>– select 函数();</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line">SELECT NOW(); <span class="comment"># 当前时间</span></span><br><span class="line">SELECT DATABASE(); <span class="comment"># 当前数据库</span></span><br><span class="line">SELECT USER(); <span class="comment"># 当前登录用户</span></span><br><span class="line">SELECT CONCAT(<span class="string">"hello world"</span>); <span class="comment"># 字符串拼接</span></span><br><span class="line">SELECT CONCAT(USER,<span class="string">"@"</span>,HOST) FROM mysql.user;</span><br><span class="line">SELECT GROUP_CONCAT(USER,<span class="string">"@"</span>,HOST) FROM mysql.user;</span><br><span class="line">select version(); <span class="comment"># 当前数据库版本号</span></span><br><span class="line"><span class="comment"># 相关函数查询，官方文档或者使用help</span></span><br><span class="line">https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html?tdsourcetag=s_pcqq_aiomsg</span><br><span class="line"></span><br><span class="line">help contents</span><br><span class="line">help functions</span><br><span class="line">help String Functions</span><br><span class="line">    </span><br><span class="line"><span class="comment"># 标准sql，其他关系型数据库，必须要有from</span></span><br><span class="line">select NOW() <span class="keyword">from</span> dual;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 计算</span></span><br><span class="line">select <span class="number">10</span>*<span class="number">100</span></span><br></pre></td></tr></table></figure>

<h4 id="默认执行顺序"><a href="#默认执行顺序" class="headerlink" title="默认执行顺序"></a>默认执行顺序</h4><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br></pre></td><td class="code"><pre><span class="line">select 列，列</span><br><span class="line"><span class="keyword">from</span> 表<span class="number">1</span>，表<span class="number">2</span>。。</span><br><span class="line">where 过滤条件<span class="number">1</span> 过滤条件<span class="number">2</span>。。</span><br><span class="line">group by 条件<span class="number">1</span> 条件<span class="number">2</span>。。</span><br><span class="line">having 过滤条件<span class="number">1</span> 过滤条件<span class="number">2</span>。。</span><br><span class="line">order by 条件列<span class="number">1</span> 条件列<span class="number">2</span></span><br><span class="line">limit 限制</span><br><span class="line"></span><br><span class="line"><span class="comment"># d</span></span><br><span class="line">select</span><br><span class="line"><span class="number">1</span> <span class="keyword">from</span> 表<span class="number">1</span>，表<span class="number">2</span>。。。</span><br><span class="line"><span class="number">2</span> where 过滤条件<span class="number">1</span> 过滤条件<span class="number">2</span>。。</span><br><span class="line"><span class="number">3</span> group by 条件<span class="number">1</span> 条件<span class="number">2</span>。。</span><br><span class="line"><span class="number">3.5</span> select_list  name,age 列名列表</span><br><span class="line"><span class="number">4</span> having 过滤条件<span class="number">1</span> 过滤条件<span class="number">2</span>。。</span><br><span class="line"><span class="number">5</span> order by 条件列<span class="number">1</span> 条件列<span class="number">2</span></span><br><span class="line"><span class="number">6</span> limit 限制</span><br><span class="line"></span><br><span class="line"></span><br><span class="line"><span class="comment"># 完整select 执行顺序</span></span><br><span class="line">(<span class="number">6</span>)  SELECT 列</span><br><span class="line">(<span class="number">8</span>)  DISTINCT &lt;select_list&gt;</span><br><span class="line">(<span class="number">1</span>)  FROM &lt;left_table&gt;</span><br><span class="line">(<span class="number">3</span>)  &lt;join_type&gt; JOIN &lt;right_table&gt;</span><br><span class="line">(<span class="number">2</span>)  ON &lt;join_condition&gt;</span><br><span class="line">(<span class="number">4</span>)  WHERE &lt;where_condition&gt;</span><br><span class="line">(<span class="number">5</span>)  GROUP BY &lt;group_by_list&gt;</span><br><span class="line">(<span class="number">7</span>)  HAVING &lt;having_condition&gt;</span><br><span class="line">(<span class="number">9</span>)  ORDER BY &lt;order_by_condition&gt;</span><br><span class="line">(<span class="number">10</span>) LIMIT &lt;limit_number&gt;</span><br></pre></td></tr></table></figure>

<h4 id="导入数据"><a href="#导入数据" class="headerlink" title="导入数据"></a>导入数据</h4><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line">mysql &lt; world.sql</span><br><span class="line">库：world</span><br><span class="line">表：</span><br><span class="line">city</span><br><span class="line">country</span><br><span class="line">countrylanguage</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">city:城市表</span><br><span class="line">DESC city;</span><br><span class="line">ID :         城市ID</span><br><span class="line">NAME :       城市名</span><br><span class="line">CountryCode: 国家代码，比如中国CHN 美国USA</span><br><span class="line">District :   区域</span><br><span class="line">Population : 人口</span><br></pre></td></tr></table></figure>



<h2 id="7-2-单表子句-from"><a href="#7-2-单表子句-from" class="headerlink" title="7.2 单表子句-from"></a>7.2 单表子句-from</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">SELECT 列1,列2 FROM 表</span><br><span class="line">SELECT  *  FROM 表</span><br></pre></td></tr></table></figure>

<p>例子:<br> – 查询city中所有的数据(不要对大表进行操作)</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT * FROM city ;</span><br></pre></td></tr></table></figure>

<p>– 查询city表中,id和姓名</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT id ,name  FROM city;</span><br></pre></td></tr></table></figure>



<h2 id="7-3-单表子句-where"><a href="#7-3-单表子句-where" class="headerlink" title="7.3 单表子句-where"></a>7.3 单表子句-where</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT col1,col2 FROM TABLE WHERE colN 条件;</span><br></pre></td></tr></table></figure>

<h3 id="7-3-1-where配合等值查询"><a href="#7-3-1-where配合等值查询" class="headerlink" title="7.3.1 where配合等值查询"></a>7.3.1 where配合等值查询</h3><p>例子:<br> – 查询中国(CHN)所有城市信息</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT * FROM city WHERE countrycode=<span class="string">'CHN'</span>;</span><br></pre></td></tr></table></figure>

<p>– 查询北京市的信息</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT * FROM city WHERE NAME=<span class="string">'peking'</span>;</span><br></pre></td></tr></table></figure>

<p>– 查询甘肃省所有城市信息</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT * FROM city WHERE district=<span class="string">'gansu'</span>;</span><br></pre></td></tr></table></figure>

<h3 id="7-3-2-where配合比较操作符-gt-lt-gt-lt-lt-gt"><a href="#7-3-2-where配合比较操作符-gt-lt-gt-lt-lt-gt" class="headerlink" title="7.3.2 where配合比较操作符(&gt; &lt; &gt;= &lt;= &lt;&gt;)"></a>7.3.2 where配合比较操作符(&gt; &lt; &gt;= &lt;= &lt;&gt;)</h3><p>例子:<br> – 查询世界上少于100人的城市</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT * FROM city WHERE population&lt;100;</span><br></pre></td></tr></table></figure>

<h3 id="7-3-3-where配合逻辑运算符-and-or"><a href="#7-3-3-where配合逻辑运算符-and-or" class="headerlink" title="7.3.3 where配合逻辑运算符(and  or )"></a>7.3.3 where配合逻辑运算符(and  or )</h3><p>例子:<br> – 中国人口数量大于500w</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT * FROM city WHERE countrycode=<span class="string">'CHN'</span> AND population&gt;5000000;</span><br></pre></td></tr></table></figure>

<p>– 中国或美国城市信息</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT * FROM city WHERE countrycode=<span class="string">'CHN'</span> OR countrycode=<span class="string">'USA'</span>;</span><br></pre></td></tr></table></figure>

<h3 id="7-3-4-where配合模糊查询"><a href="#7-3-4-where配合模糊查询" class="headerlink" title="7.3.4 where配合模糊查询"></a>7.3.4 where配合模糊查询</h3><p>例子:<br> – 查询省的名字前面带guang开头的</p>
<figure class="highlight ruby"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">SELECT * FROM city WHERE district LIKE <span class="string">'guang%'</span>;    </span><br><span class="line">注意<span class="symbol">:%</span>不能放在前面,因为不走索引.只能用字符串的列</span><br></pre></td></tr></table></figure>

<h3 id="7-3-5-where配合in语句"><a href="#7-3-5-where配合in语句" class="headerlink" title="7.3.5 where配合in语句"></a>7.3.5 where配合in语句</h3><p>– 中国或美国城市信息</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">SELECT * FROM city WHERE countrycode =<span class="string">'CHN'</span>  <span class="keyword">or</span> countrycode =<span class="string">'USA'</span>;</span><br><span class="line">SELECT * FROM city WHERE countrycode IN (<span class="string">'CHN'</span> ,<span class="string">'USA'</span>);</span><br><span class="line"><span class="comment"># 错误</span></span><br><span class="line">SELECT * FROM city WHERE countrycode =<span class="string">'CHN'</span>  <span class="keyword">or</span> countrycode =<span class="string">'USA'</span>AND population&gt;<span class="number">5000000</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 正确</span></span><br><span class="line">SELECT * FROM city WHERE countrycode IN (<span class="string">'CHN'</span> ,<span class="string">'USA'</span>) AND population&gt;<span class="number">5000000</span>;</span><br></pre></td></tr></table></figure>

<h3 id="7-3-6-where配合between-and"><a href="#7-3-6-where配合between-and" class="headerlink" title="7.3.6 where配合between and"></a>7.3.6 where配合between and</h3><p>例子:<br> – 查询世界上人口数量大于100w小于200w的城市信息</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">SELECT * FROM city  WHERE population &gt;1000000 AND population &lt;2000000;</span><br><span class="line">SELECT * FROM city  WHERE population BETWEEN 1000000 AND 2000000;</span><br></pre></td></tr></table></figure>

<h2 id="7-4-group-by-常用聚合函数"><a href="#7-4-group-by-常用聚合函数" class="headerlink" title="7.4 group by + 常用聚合函数"></a>7.4 group by + 常用聚合函数</h2><h3 id="7-4-1-作用"><a href="#7-4-1-作用" class="headerlink" title="7.4.1 作用"></a>7.4.1 作用</h3><figure class="highlight csharp"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">根据 <span class="keyword">by</span>后面的条件进行分组，方便统计，<span class="keyword">by</span>后面跟一个列或多个列</span><br></pre></td></tr></table></figure>

<img src="https://tva1.sinaimg.cn/large/007S8ZIlgy1gicgcn3ezej31wr0u0qte.jpg" alt="image-20200813182847212" style="zoom:50%;" />

<h3 id="7-4-2-常用聚合函数"><a href="#7-4-2-常用聚合函数" class="headerlink" title="7.4.2 常用聚合函数"></a>7.4.2 常用聚合函数</h3><figure class="highlight swift"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">**<span class="built_in">max</span>()**      ：最大值</span><br><span class="line">**<span class="built_in">min</span>()**      ：最小值</span><br><span class="line">**avg()**      ：平均值</span><br><span class="line">**sum()**      ：总和</span><br><span class="line">**<span class="built_in">count</span>()**    ：个数</span><br><span class="line">group_concat() : 列转行</span><br></pre></td></tr></table></figure>

<h3 id="7-4-3-例子："><a href="#7-4-3-例子：" class="headerlink" title="7.4.3 例子："></a>7.4.3 例子：</h3><p>例子1：统计世界上每个国家的总人口数.</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">USE</span> <span class="title">world</span></span><br><span class="line"><span class="title">SELECT</span> <span class="title">countrycode</span> ,<span class="title">SUM</span>(<span class="title">population</span>)    <span class="title">FROM</span>  <span class="title">city</span>  <span class="title">GROUP</span> <span class="title">BY</span> <span class="title">countrycode</span>;</span><br></pre></td></tr></table></figure>

<p>例子2： 统计中国各个省的总人口数量(练习)</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT district,SUM(Population) FROM city  WHERE countrycode=<span class="string">'chn'</span> GROUP BY district;</span><br></pre></td></tr></table></figure>

<p>例子3：统计世界上每个国家的城市数量(练习)</p>
<figure class="highlight objectivec"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT countrycode,COUNT(<span class="keyword">id</span>)  FROM city GROUP BY countrycode;</span><br></pre></td></tr></table></figure>

<p>例子4：统计中国，每个省总人口，城市个数，城市名列表(重点)</p>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">select district,sum(Population),count(id),name <span class="keyword">from</span> world.city where countrycode=<span class="string">'CHN'</span> group by district;</span><br><span class="line"><span class="comment"># 报错：</span></span><br><span class="line">ERROR <span class="number">1055</span> (<span class="number">42000</span>): Expression <span class="comment">#4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'world.city.Name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by</span></span><br><span class="line"><span class="comment"># 原因：违反了sql_mode=only_full_group_by，SELECT list必须是group by的字段和聚合函数，name不属于，因为name有很多，在mysql5.6之前可以查询，但是只取name的第一个，5.7以后直接报错，这是合理的</span></span><br><span class="line">select district,sum(Population),count(id),group_concat(name) <span class="keyword">from</span> world.city where countrycode=<span class="string">'CHN'</span> group by district;</span><br></pre></td></tr></table></figure>



<h2 id="7-5-having"><a href="#7-5-having" class="headerlink" title="7.5 having"></a>7.5 having</h2><figure class="highlight csharp"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">where</span>|<span class="keyword">group</span>|having</span><br></pre></td></tr></table></figure>

<p>例子4：统计中国每个省的总人口数，只打印总人口数小于100w</p>
<figure class="highlight csharp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SELECT district,SUM(Population)</span><br><span class="line">FROM city</span><br><span class="line">WHERE countrycode=<span class="string">'chn'</span></span><br><span class="line">GROUP BY district</span><br><span class="line"><span class="function">HAVING <span class="title">SUM</span>(<span class="params">Population</span>) &lt; 1000000</span> ;</span><br></pre></td></tr></table></figure>

<h2 id="7-6-order-by-limit"><a href="#7-6-order-by-limit" class="headerlink" title="7.6 order by + limit"></a>7.6 order by + limit</h2><h3 id="7-6-1-作用"><a href="#7-6-1-作用" class="headerlink" title="7.6.1 作用"></a>7.6.1 作用</h3><figure class="highlight csharp"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">实现先排序，<span class="keyword">by</span>后添加条件列</span><br></pre></td></tr></table></figure>

<h3 id="7-6-2-应用案例"><a href="#7-6-2-应用案例" class="headerlink" title="7.6.2 应用案例"></a>7.6.2 应用案例</h3><ol>
<li>查看中国所有的城市，并按人口数进行排序(从大到小)</li>
</ol>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">SELECT * FROM city WHERE countrycode=<span class="string">'CHN'</span> ORDER BY population DESC;</span><br></pre></td></tr></table></figure>

<ol>
<li>统计中国各个省的总人口数量，按照总人口从大到小排序</li>
</ol>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SELECT district <span class="keyword">AS</span> 省 ,SUM(Population) <span class="keyword">AS</span> 总人口</span><br><span class="line">FROM city</span><br><span class="line">WHERE countrycode=<span class="string">'chn'</span></span><br><span class="line">GROUP BY district</span><br><span class="line">ORDER BY 总人口 DESC ;</span><br></pre></td></tr></table></figure>

<ol>
<li>统计中国,每个省的总人口,找出总人口大于500w的,并按总人口从大到小排序,只显示前三名</li>
</ol>
<figure class="highlight csharp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line">SELECT  district, SUM(population)  FROM  city </span><br><span class="line">WHERE countrycode=<span class="string">'CHN'</span></span><br><span class="line">GROUP BY district </span><br><span class="line"><span class="function">HAVING <span class="title">SUM</span>(<span class="params">population</span>)&gt;5000000</span></span><br><span class="line"><span class="function">ORDER BY <span class="title">SUM</span>(<span class="params">population</span>) DESC</span></span><br><span class="line"><span class="function">LIMIT 3</span> ;</span><br><span class="line"></span><br><span class="line">LIMIT N ,M ---&gt;跳过N,显示一共M行</span><br><span class="line">LIMIT <span class="number">5</span>,<span class="number">5</span></span><br><span class="line">LIMIT <span class="number">5</span> OFFSET <span class="number">5</span>;</span><br><span class="line"></span><br><span class="line">SELECT  district, SUM(population)  FROM  city </span><br><span class="line">WHERE countrycode=<span class="string">'CHN'</span></span><br><span class="line">GROUP BY district </span><br><span class="line"><span class="function">HAVING <span class="title">SUM</span>(<span class="params">population</span>)&gt;5000000</span></span><br><span class="line"><span class="function">ORDER BY <span class="title">SUM</span>(<span class="params">population</span>) DESC</span></span><br><span class="line"><span class="function">LIMIT 5,5</span>;</span><br></pre></td></tr></table></figure>

<h2 id="7-7-distinct：去重复"><a href="#7-7-distinct：去重复" class="headerlink" title="7.7 distinct：去重复"></a>7.7 distinct：去重复</h2><figure class="highlight cpp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">SELECT countrycode FROM city ;</span><br><span class="line"><span class="function">SELECT <span class="title">DISTINCT</span><span class="params">(countrycode)</span> FROM city  </span>;</span><br></pre></td></tr></table></figure>

<h2 id="7-8-联合查询-union-all"><a href="#7-8-联合查询-union-all" class="headerlink" title="7.8 联合查询- union all"></a>7.8 联合查询- union all</h2><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 求并集</span></span><br><span class="line">-- 中国或美国城市信息</span><br><span class="line">SELECT * FROM city </span><br><span class="line">WHERE countrycode IN (<span class="string">'CHN'</span> ,<span class="string">'USA'</span>);</span><br><span class="line"></span><br><span class="line">SELECT * FROM city </span><br><span class="line">WHERE countrycode =<span class="string">'CHN'</span> <span class="keyword">or</span> countrycode =<span class="string">'USA'</span>;</span><br><span class="line"></span><br><span class="line">SELECT * FROM city WHERE countrycode=<span class="string">'CHN'</span></span><br><span class="line">UNION ALL</span><br><span class="line">SELECT * FROM city WHERE countrycode=<span class="string">'USA'</span></span><br><span class="line"></span><br><span class="line">说明:一般情况下,我们会将 IN 或者 OR 语句 改写成 UNION ALL,来提高性能</span><br><span class="line">都是聚合两个结果集</span><br><span class="line">UNION     去重复</span><br><span class="line">UNION ALL 不去重复</span><br></pre></td></tr></table></figure>

<h2 id="7-9-join-多表连接查询"><a href="#7-9-join-多表连接查询" class="headerlink" title="7.9 join 多表连接查询"></a>7.9 join 多表连接查询</h2><h3 id="7-9-0-案例准备"><a href="#7-9-0-案例准备" class="headerlink" title="7.9.0 案例准备"></a>7.9.0 案例准备</h3><p>按需求创建一下表结构:</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br><span class="line">90</span><br><span class="line">91</span><br><span class="line">92</span><br><span class="line">93</span><br><span class="line">94</span><br><span class="line">95</span><br><span class="line">96</span><br><span class="line">97</span><br><span class="line">98</span><br><span class="line">99</span><br><span class="line">100</span><br><span class="line">101</span><br><span class="line">102</span><br><span class="line">103</span><br><span class="line">104</span><br><span class="line">105</span><br><span class="line">106</span><br><span class="line">107</span><br><span class="line">108</span><br><span class="line">109</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">use</span> <span class="title">school</span></span><br><span class="line"><span class="title">student</span> ：学生表</span><br><span class="line"><span class="title">sno</span>：    学号</span><br><span class="line"><span class="title">sname</span>：学生姓名</span><br><span class="line"><span class="title">sage</span>： 学生年龄</span><br><span class="line"><span class="title">ssex</span>： 学生性别</span><br><span class="line"></span><br><span class="line"><span class="title">teacher</span> ：教师表</span><br><span class="line"><span class="title">tno</span>：     教师编号</span><br><span class="line"><span class="title">tname</span>：教师名字</span><br><span class="line"></span><br><span class="line"><span class="title">course</span> ：课程表</span><br><span class="line"><span class="title">cno</span>：  课程编号</span><br><span class="line"><span class="title">cname</span>：课程名字</span><br><span class="line"><span class="title">tno</span>：  教师编号</span><br><span class="line"></span><br><span class="line"><span class="title">score</span>  ：成绩表</span><br><span class="line"><span class="title">sno</span>：  学号</span><br><span class="line"><span class="title">cno</span>：  课程编号</span><br><span class="line"><span class="title">score</span>：成绩</span><br><span class="line"></span><br><span class="line">-- 项目构建</span><br><span class="line"><span class="title">drop</span> <span class="title">database</span> <span class="title">school</span>;</span><br><span class="line">CREATE DATABASE school CHARSET utf8;</span><br><span class="line"><span class="keyword">USE</span> <span class="title">school</span></span><br><span class="line"></span><br><span class="line"><span class="title">CREATE</span> <span class="title">TABLE</span> <span class="title">student</span>(</span><br><span class="line"><span class="title">sno</span> <span class="title">INT</span> <span class="title">NOT</span> <span class="title">NULL</span> <span class="title">PRIMARY</span> <span class="title">KEY</span> <span class="title">AUTO_INCREMENT</span> <span class="title">COMMENT</span> '学号',</span><br><span class="line"><span class="title">sname</span> <span class="title">VARCHAR</span>(20) <span class="title">NOT</span> <span class="title">NULL</span> <span class="title">COMMENT</span> '姓名',</span><br><span class="line"><span class="title">sage</span> <span class="title">TINYINT</span> <span class="title">UNSIGNED</span>  <span class="title">NOT</span> <span class="title">NULL</span> <span class="title">COMMENT</span> '年龄',</span><br><span class="line"><span class="title">ssex</span>  <span class="title">ENUM</span>('<span class="title">f</span>','<span class="title">m</span>') <span class="title">NOT</span> <span class="title">NULL</span> <span class="title">DEFAULT</span> '<span class="title">m</span>' <span class="title">COMMENT</span> '性别'</span><br><span class="line">)<span class="title">ENGINE</span>=<span class="title">INNODB</span> <span class="title">CHARSET</span>=<span class="title">utf8</span>;</span><br><span class="line"></span><br><span class="line">CREATE TABLE course(</span><br><span class="line">cno INT NOT <span class="keyword">NULL</span> PRIMARY KEY COMMENT <span class="string">'课程编号'</span>,</span><br><span class="line">cname VARCHAR(<span class="number">20</span>) NOT <span class="keyword">NULL</span> COMMENT <span class="string">'课程名字'</span>,</span><br><span class="line">tno INT NOT <span class="keyword">NULL</span>  COMMENT <span class="string">'教师编号'</span></span><br><span class="line">)ENGINE=INNODB CHARSET utf8;</span><br><span class="line"></span><br><span class="line">CREATE TABLE sc (</span><br><span class="line">sno INT NOT <span class="keyword">NULL</span> COMMENT <span class="string">'学号'</span>,</span><br><span class="line">cno INT NOT <span class="keyword">NULL</span> COMMENT <span class="string">'课程编号'</span>,</span><br><span class="line">score INT  NOT <span class="keyword">NULL</span> <span class="keyword">DEFAULT</span> <span class="number">0</span> COMMENT <span class="string">'成绩'</span></span><br><span class="line">)ENGINE=INNODB CHARSET=utf8;</span><br><span class="line"></span><br><span class="line">CREATE TABLE teacher(</span><br><span class="line">tno INT NOT <span class="keyword">NULL</span> PRIMARY KEY COMMENT <span class="string">'教师编号'</span>,</span><br><span class="line">tname VARCHAR(<span class="number">20</span>) NOT <span class="keyword">NULL</span> COMMENT <span class="string">'教师名字'</span></span><br><span class="line">)ENGINE=INNODB CHARSET utf8;</span><br><span class="line"></span><br><span class="line">INSERT INTO student(sno,sname,sage,ssex)</span><br><span class="line">VALUES (<span class="number">1</span>,<span class="string">'zhang3'</span>,<span class="number">18</span>,<span class="string">'m'</span>);</span><br><span class="line"></span><br><span class="line">INSERT INTO student(sno,sname,sage,ssex)</span><br><span class="line">VALUES</span><br><span class="line">(<span class="number">2</span>,<span class="string">'zhang4'</span>,<span class="number">18</span>,<span class="string">'m'</span>),</span><br><span class="line">(<span class="number">3</span>,<span class="string">'li4'</span>,<span class="number">18</span>,<span class="string">'m'</span>),</span><br><span class="line">(<span class="number">4</span>,<span class="string">'wang5'</span>,<span class="number">19</span>,<span class="string">'f'</span>);</span><br><span class="line"></span><br><span class="line">INSERT INTO student</span><br><span class="line">VALUES</span><br><span class="line">(<span class="number">5</span>,<span class="string">'zh4'</span>,<span class="number">18</span>,<span class="string">'m'</span>),</span><br><span class="line">(<span class="number">6</span>,<span class="string">'zhao4'</span>,<span class="number">18</span>,<span class="string">'m'</span>),</span><br><span class="line">(<span class="number">7</span>,<span class="string">'ma6'</span>,<span class="number">19</span>,<span class="string">'f'</span>);</span><br><span class="line"></span><br><span class="line">INSERT INTO student(sname,sage,ssex)</span><br><span class="line">VALUES</span><br><span class="line">(<span class="string">'oldboy'</span>,<span class="number">20</span>,<span class="string">'m'</span>),</span><br><span class="line">(<span class="string">'oldgirl'</span>,<span class="number">20</span>,<span class="string">'f'</span>),</span><br><span class="line">(<span class="string">'oldp'</span>,<span class="number">25</span>,<span class="string">'m'</span>);</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">INSERT INTO teacher(tno,tname) VALUES</span><br><span class="line">(<span class="number">101</span>,<span class="string">'oldboy'</span>),</span><br><span class="line">(<span class="number">102</span>,<span class="string">'hesw'</span>),</span><br><span class="line">(<span class="number">103</span>,<span class="string">'oldguo'</span>);</span><br><span class="line"></span><br><span class="line">DESC course;</span><br><span class="line">INSERT INTO course(cno,cname,tno)</span><br><span class="line">VALUES</span><br><span class="line">(<span class="number">1001</span>,<span class="string">'linux'</span>,<span class="number">101</span>),</span><br><span class="line">(<span class="number">1002</span>,<span class="string">'python'</span>,<span class="number">102</span>),</span><br><span class="line">(<span class="number">1003</span>,<span class="string">'mysql'</span>,<span class="number">103</span>);</span><br><span class="line"></span><br><span class="line">DESC sc;</span><br><span class="line">INSERT INTO sc(sno,cno,score)</span><br><span class="line">VALUES</span><br><span class="line">(<span class="number">1</span>,<span class="number">1001</span>,<span class="number">80</span>),</span><br><span class="line">(<span class="number">1</span>,<span class="number">1002</span>,<span class="number">59</span>),</span><br><span class="line">(<span class="number">2</span>,<span class="number">1002</span>,<span class="number">90</span>),</span><br><span class="line">(<span class="number">2</span>,<span class="number">1003</span>,<span class="number">100</span>),</span><br><span class="line">(<span class="number">3</span>,<span class="number">1001</span>,<span class="number">99</span>),</span><br><span class="line">(<span class="number">3</span>,<span class="number">1003</span>,<span class="number">40</span>),</span><br><span class="line">(<span class="number">4</span>,<span class="number">1001</span>,<span class="number">79</span>),</span><br><span class="line">(<span class="number">4</span>,<span class="number">1002</span>,<span class="number">61</span>),</span><br><span class="line">(<span class="number">4</span>,<span class="number">1003</span>,<span class="number">99</span>),</span><br><span class="line">(<span class="number">5</span>,<span class="number">1003</span>,<span class="number">40</span>),</span><br><span class="line">(<span class="number">6</span>,<span class="number">1001</span>,<span class="number">89</span>),</span><br><span class="line">(<span class="number">6</span>,<span class="number">1003</span>,<span class="number">77</span>),</span><br><span class="line">(<span class="number">7</span>,<span class="number">1001</span>,<span class="number">67</span>),</span><br><span class="line">(<span class="number">7</span>,<span class="number">1003</span>,<span class="number">82</span>),</span><br><span class="line">(<span class="number">8</span>,<span class="number">1001</span>,<span class="number">70</span>),</span><br><span class="line">(<span class="number">9</span>,<span class="number">1003</span>,<span class="number">80</span>),</span><br><span class="line">(<span class="number">10</span>,<span class="number">1003</span>,<span class="number">96</span>);</span><br><span class="line"></span><br><span class="line">SELECT * FROM student;</span><br><span class="line">SELECT * FROM teacher;</span><br><span class="line">SELECT * FROM course;</span><br><span class="line">SELECT * FROM sc;</span><br></pre></td></tr></table></figure>

<h3 id="7-9-1-语法"><a href="#7-9-1-语法" class="headerlink" title="7.9.1 语法"></a>7.9.1 语法</h3><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">笛卡尔积：select * <span class="keyword">from</span> teacher，course；</span><br><span class="line">内连接(inner join)：</span><br><span class="line">select * <span class="keyword">from</span> teacher join course on teacher.tno=course.tno;</span><br><span class="line">select city.name,country.name,city.population <span class="keyword">from</span> city join country on city.countrycode = country.code <span class="keyword">and</span> city.population &lt;<span class="number">100</span>;</span><br><span class="line">外连接:</span><br><span class="line">  left join:左表所有数据和右表满足条件的数据</span><br><span class="line">  	select * <span class="keyword">from</span> teacher join course on teacher.tno=course.tno;</span><br><span class="line">    select city.name,country.name,city.population <span class="keyword">from</span> city left join country on city.countrycode = country.code <span class="keyword">and</span> city.population &lt;<span class="number">100</span>;</span><br><span class="line">  right join：右表所有数据和左表满足条件的数据</span><br><span class="line">  	select city.name,country.name,city.population <span class="keyword">from</span> city right join country on city.countrycode = country.code <span class="keyword">and</span> city.population &lt;<span class="number">100</span>;</span><br></pre></td></tr></table></figure>



<img src="https://tva1.sinaimg.cn/large/007S8ZIlgy1gicgcvxgdqj31uo0ik4gj.jpg" alt="image-20200813203607215" style="zoom:50%;" />

<p>查询张三的家庭住址</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">SELECT A.name,B.address FROM</span><br><span class="line">A JOIN  B</span><br><span class="line">ON A.id=B.id</span><br><span class="line">WHERE A.name=<span class="string">'zhangsan'</span></span><br></pre></td></tr></table></figure>

<h3 id="7-9-2-例子："><a href="#7-9-2-例子：" class="headerlink" title="7.9.2 例子："></a>7.9.2 例子：</h3><ol>
<li>查询一下世界上人口数量小于100人的城市名和国家名</li>
</ol>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">SELECT b.name ,a.name ,a.population</span><br><span class="line">FROM  city  <span class="keyword">AS</span> a</span><br><span class="line">JOIN  country <span class="keyword">AS</span> b</span><br><span class="line">ON    b.code=a.countrycode</span><br><span class="line">WHERE  a.Population&lt;<span class="number">100</span></span><br></pre></td></tr></table></figure>

<ol>
<li>查询城市shenyang，城市人口，所在国家名（name）及国土面积（SurfaceArea）</li>
</ol>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">SELECT a.name,a.population,b.name ,b.SurfaceArea</span><br><span class="line">FROM city  <span class="keyword">AS</span> a JOIN country <span class="keyword">AS</span> b</span><br><span class="line">ON a.countrycode=b.code</span><br><span class="line">WHERE a.name=<span class="string">'shenyang'</span>;</span><br></pre></td></tr></table></figure>

<h3 id="7-9-3-别名"><a href="#7-9-3-别名" class="headerlink" title="7.9.3 别名"></a>7.9.3 别名</h3><figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">列别名,表别名</span><br><span class="line">SELECT </span><br><span class="line">a.Name <span class="keyword">AS</span> an ,</span><br><span class="line">b.name <span class="keyword">AS</span> bn ,</span><br><span class="line">b.SurfaceArea <span class="keyword">AS</span> bs,</span><br><span class="line">a.Population <span class="keyword">AS</span> bp</span><br><span class="line">FROM city <span class="keyword">AS</span> a  JOIN country <span class="keyword">AS</span> b</span><br><span class="line">ON a.CountryCode=b.Code</span><br><span class="line">WHERE a.name =<span class="string">'shenyang'</span>;</span><br></pre></td></tr></table></figure>

<h3 id="7-9-4-多表SQL练习题"><a href="#7-9-4-多表SQL练习题" class="headerlink" title="7.9.4 多表SQL练习题"></a>7.9.4 多表SQL练习题</h3><ol>
<li>统计zhang3,学习了几门课</li>
</ol>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">SELECT st.sname , COUNT(sc.cno)</span><br><span class="line">FROM student <span class="keyword">AS</span> st</span><br><span class="line">JOIN</span><br><span class="line">sc</span><br><span class="line">ON st.sno=sc.sno</span><br><span class="line">WHERE st.sname=<span class="string">'zhang3'</span></span><br></pre></td></tr></table></figure>

<ol>
<li>查询zhang3,学习的课程名称有哪些?</li>
</ol>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">SELECT st.sname , GROUP_CONCAT(co.cname)</span><br><span class="line">FROM student <span class="keyword">AS</span> st</span><br><span class="line">JOIN sc</span><br><span class="line">ON st.sno=sc.sno</span><br><span class="line">JOIN course <span class="keyword">AS</span> co</span><br><span class="line">ON sc.cno=co.cno</span><br><span class="line">WHERE st.sname=<span class="string">'zhang3'</span></span><br></pre></td></tr></table></figure>

<ol>
<li>查询oldguo老师教的学生名.</li>
</ol>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">SELECT te.tname ,GROUP_CONCAT(st.sname)</span><br><span class="line">FROM student <span class="keyword">AS</span> st</span><br><span class="line">JOIN sc</span><br><span class="line">ON st.sno=sc.sno</span><br><span class="line">JOIN course <span class="keyword">AS</span> co</span><br><span class="line">ON sc.cno=co.cno</span><br><span class="line">JOIN teacher <span class="keyword">AS</span> te</span><br><span class="line">ON co.tno=te.tno</span><br><span class="line">WHERE te.tname=<span class="string">'oldguo'</span>;</span><br></pre></td></tr></table></figure>

<ol>
<li>查询oldguo所教课程的平均分数</li>
</ol>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">SELECT te.tname,AVG(sc.score)</span><br><span class="line">FROM teacher <span class="keyword">AS</span> te</span><br><span class="line">JOIN course <span class="keyword">AS</span> co</span><br><span class="line">ON te.tno=co.tno</span><br><span class="line">JOIN sc</span><br><span class="line">ON co.cno=sc.cno</span><br><span class="line">WHERE te.tname=<span class="string">'oldguo'</span></span><br></pre></td></tr></table></figure>

<p>4.1 每位老师所教课程的平均分,并按平均分排序</p>
<figure class="highlight objectivec"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">SELECT te.tname,<span class="built_in">AVG</span>(sc.score)</span><br><span class="line">FROM teacher AS te</span><br><span class="line">JOIN course AS co</span><br><span class="line">ON te.tno=co.tno</span><br><span class="line">JOIN sc</span><br><span class="line">ON co.cno=sc.cno</span><br><span class="line">GROUP BY te.tname</span><br><span class="line">ORDER BY <span class="built_in">AVG</span>(sc.score) DESC ;</span><br></pre></td></tr></table></figure>

<ol>
<li>查询oldguo所教的不及格的学生姓名</li>
</ol>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">SELECT te.tname,st.sname,sc.score</span><br><span class="line">FROM teacher <span class="keyword">AS</span> te</span><br><span class="line">JOIN course  <span class="keyword">AS</span> co</span><br><span class="line">ON te.tno=co.tno</span><br><span class="line">JOIN sc</span><br><span class="line">ON co.cno=sc.cno</span><br><span class="line">JOIN student <span class="keyword">AS</span> st</span><br><span class="line">ON sc.sno=st.sno</span><br><span class="line">WHERE te.tname=<span class="string">'oldguo'</span> <span class="keyword">AND</span> sc.score&lt;<span class="number">60</span>;</span><br></pre></td></tr></table></figure>

<p>5.1 查询所有老师所教学生不及格的信息</p>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">SELECT te.tname,st.sname,sc.score</span><br><span class="line">FROM teacher <span class="keyword">AS</span> te</span><br><span class="line">JOIN course  <span class="keyword">AS</span> co</span><br><span class="line">ON te.tno=co.tno</span><br><span class="line">JOIN sc</span><br><span class="line">ON co.cno=sc.cno</span><br><span class="line">JOIN student <span class="keyword">AS</span> st</span><br><span class="line">ON sc.sno=st.sno</span><br><span class="line">WHERE sc.score&lt;<span class="number">60</span>;</span><br></pre></td></tr></table></figure>

<h3 id="注意：-1"><a href="#注意：-1" class="headerlink" title="注意："></a>注意：</h3><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line">join 左边的为左表，右边的为右边</span><br><span class="line"></span><br><span class="line">驱动表：驱动表会拿着每一行，逐一的跟另一个表匹配，匹配成功就合并</span><br><span class="line"><span class="comment">#双层for循环</span></span><br><span class="line"><span class="keyword">for</span> 每一行 <span class="keyword">in</span> 驱动表：</span><br><span class="line">	<span class="keyword">for</span> 每一行 <span class="keyword">in</span> 另一个表：</span><br><span class="line">		匹配成功合并</span><br><span class="line"><span class="comment"># 假设驱动表是1000行，另一个表是10行，至少要1000次循环，如果拿另一个表去找，最少10次</span></span><br><span class="line">多表关联，驱动表要选小表，降低next loop次数</span><br><span class="line"></span><br><span class="line">对于内连接来讲，我们没法控制驱动表是谁，完全由优化器决定，如果要人为干预，要把内连接写成外连接的方式</span><br><span class="line">如果使用left join 可以强制左表为驱动表</span><br><span class="line"></span><br><span class="line">总结：</span><br><span class="line"><span class="number">1</span> 小表作为驱动表，降低next loop次数</span><br><span class="line"><span class="number">2</span> left join 可以强制左表为驱动表</span><br></pre></td></tr></table></figure>

<h3 id="7-9-5-综合练习"><a href="#7-9-5-综合练习" class="headerlink" title="7.9.5 综合练习"></a>7.9.5 综合练习</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">1. 查询平均成绩大于60分的同学的学号和平均成绩；</span><br><span class="line">2. 查询所有同学的学号、姓名、选课数、总成绩；</span><br><span class="line">3. 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 </span><br><span class="line">4. 统计各位老师,所教课程的及格率</span><br><span class="line">5. 查询每门课程被选修的学生数</span><br><span class="line">6. 查询出只选修了一门课程的全部学生的学号和姓名</span><br><span class="line">7. 查询选修课程门数超过1门的学生信息</span><br><span class="line">8. 统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表</span><br><span class="line">9. 查询平均成绩大于85的所有学生的学号、姓名和平均成绩</span><br></pre></td></tr></table></figure>

<h3 id="7-9-6-补充"><a href="#7-9-6-补充" class="headerlink" title="7.9.6 补充"></a>7.9.6 补充</h3><h4 id="别名"><a href="#别名" class="headerlink" title="别名"></a>别名</h4><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 1 表别名</span></span><br><span class="line">SELECT te.tname,st.sname,sc.score</span><br><span class="line">FROM teacher AS te</span><br><span class="line">JOIN course  AS co</span><br><span class="line">ON te.tno=co.tno</span><br><span class="line">JOIN sc</span><br><span class="line">ON co.cno=sc.cno</span><br><span class="line">JOIN student AS st</span><br><span class="line">ON sc.sno=st.sno</span><br><span class="line">WHERE sc.score&lt;<span class="number">60</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment"># 表别名可以在全局使用，只在当次查询中有用</span></span><br><span class="line"></span><br><span class="line"><span class="comment"># 2 列别名</span></span><br><span class="line">select student.sno <span class="keyword">as</span> <span class="string">'学号'</span>,student.sname <span class="keyword">as</span> <span class="string">'姓名'</span> <span class="keyword">from</span> studnet;</span><br><span class="line"><span class="comment"># 列别名在哪些子句中可以使用</span></span><br><span class="line">在having之前都不能使用（参照上面select执行顺序）</span><br></pre></td></tr></table></figure>

<h2 id=""><a href="#" class="headerlink" title=""></a></h2><h1 id="八-元数据信息"><a href="#八-元数据信息" class="headerlink" title="八 元数据信息"></a>八 元数据信息</h1><h2 id="8-1-逻辑表有关组成部分"><a href="#8-1-逻辑表有关组成部分" class="headerlink" title="8.1 逻辑表有关组成部分"></a>8.1 逻辑表有关组成部分</h2><img src="https://tva1.sinaimg.cn/large/007S8ZIlgy1gicgd5bia4j317u0u0drh.jpg" alt="image-20200814010107629" style="zoom:50%;" />

<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment"># 每次数据库启动，会自动在内存中生成nformation_schema，生成查询mysql部分元数据的视图</span></span><br><span class="line"><span class="comment"># 视图：select 语句的执行方法，不保存数据本身</span></span><br><span class="line"><span class="comment">## 创建视图</span></span><br><span class="line">create view v_city <span class="keyword">as</span> select name <span class="keyword">from</span> city where id &lt;<span class="number">10</span>;</span><br><span class="line"><span class="comment">## 使用视图</span></span><br><span class="line">select * <span class="keyword">from</span> v_city;</span><br></pre></td></tr></table></figure>



<h2 id="8-2-information-schema-tables视图"><a href="#8-2-information-schema-tables视图" class="headerlink" title="8.2 information_schema.tables视图"></a>8.2 information_schema.tables视图</h2><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br></pre></td><td class="code"><pre><span class="line">use information_schema; <span class="comment"># 进入information_schema</span></span><br><span class="line">show tables; <span class="comment"># 可以看到所有视图</span></span><br><span class="line"><span class="comment"># 我们研究tables这个视图</span></span><br><span class="line">desc tables;</span><br><span class="line"><span class="comment"># DESC information_schema.TABLES</span></span><br><span class="line">TABLE_SCHEMA    ----&gt;表所在库名</span><br><span class="line">TABLE_NAME      ----&gt;表名</span><br><span class="line">ENGINE          ----&gt;引擎</span><br><span class="line">TABLE_ROWS      ----&gt;表的行数(不是特别实时)</span><br><span class="line">AVG_ROW_LENGTH  ----&gt;表中行的平均行长度（字节）</span><br><span class="line">INDEX_LENGTH    ----&gt;索引的占用空间大小（字节）</span><br><span class="line">DATA_FREE       ----&gt;表中是否有碎片</span><br><span class="line"></span><br><span class="line"><span class="comment"># 所有的</span></span><br><span class="line">TABLE_CATALOG  </span><br><span class="line">TABLE_SCHEMA   </span><br><span class="line">TABLE_NAME     </span><br><span class="line">TABLE_TYPE     </span><br><span class="line">ENGINE         </span><br><span class="line">VERSION        </span><br><span class="line">ROW_FORMAT     </span><br><span class="line">TABLE_ROWS     </span><br><span class="line">AVG_ROW_LENGTH </span><br><span class="line">DATA_LENGTH    </span><br><span class="line">MAX_DATA_LENGTH</span><br><span class="line">INDEX_LENGTH   </span><br><span class="line">DATA_FREE      </span><br><span class="line">AUTO_INCREMENT </span><br><span class="line">CREATE_TIME    </span><br><span class="line">UPDATE_TIME    </span><br><span class="line">CHECK_TIME     </span><br><span class="line">TABLE_COLLATION</span><br><span class="line">CHECKSUM       </span><br><span class="line">CREATE_OPTIONS </span><br><span class="line">TABLE_COMMENT </span><br><span class="line"></span><br><span class="line"><span class="comment"># 使用场景</span></span><br><span class="line">资产统计，自动化运维平台，多少个库，多少个表，占用空间怎么样，统计表增长</span><br></pre></td></tr></table></figure>



<ol>
<li>查询整个数据库中所有库和所对应的表信息</li>
</ol>
<figure class="highlight css"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"></span><br><span class="line"><span class="selector-tag">SELECT</span> <span class="selector-tag">table_schema</span>,<span class="selector-tag">GROUP_CONCAT</span>(<span class="selector-tag">table_name</span>)</span><br><span class="line"><span class="selector-tag">FROM</span>  <span class="selector-tag">information_schema</span><span class="selector-class">.tables</span></span><br><span class="line"><span class="selector-tag">GROUP</span> <span class="selector-tag">BY</span> <span class="selector-tag">table_schema</span>;</span><br></pre></td></tr></table></figure>

<ol>
<li>统计所有库下的表个数</li>
</ol>
<figure class="highlight css"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="selector-tag">SELECT</span> <span class="selector-tag">table_schema</span>,<span class="selector-tag">COUNT</span>(<span class="selector-tag">table_name</span>)</span><br><span class="line"><span class="selector-tag">FROM</span> <span class="selector-tag">information_schema</span><span class="selector-class">.TABLES</span></span><br><span class="line"><span class="selector-tag">GROUP</span> <span class="selector-tag">BY</span> <span class="selector-tag">table_schema</span></span><br></pre></td></tr></table></figure>

<ol>
<li>查询所有innodb引擎的表及所在的库</li>
</ol>
<figure class="highlight go"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">SELECT table_schema,table_name,ENGINE FROM information_schema.<span class="string">`TABLES`</span></span><br><span class="line">WHERE ENGINE=<span class="string">'innodb'</span>;</span><br></pre></td></tr></table></figure>

<ol>
<li>统计world数据库下每张表的磁盘空间占用</li>
</ol>
<figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">SELECT table_name,CONCAT((TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/<span class="number">1024</span>,<span class="string">" KB"</span>)  <span class="keyword">AS</span> size_KB</span><br><span class="line">FROM information_schema.tables WHERE TABLE_SCHEMA=<span class="string">'world'</span>;</span><br></pre></td></tr></table></figure>

<ol>
<li>统计所有数据库的总的磁盘空间占用</li>
</ol>
<figure class="highlight objectivec"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">SELECT</span><br><span class="line">TABLE_SCHEMA,</span><br><span class="line">CONCAT(SUM(TABLE_ROWS*<span class="built_in">AVG_ROW_LENGTH</span>+INDEX_LENGTH)/<span class="number">1024</span>,<span class="string">" KB"</span>) AS Total_KB</span><br><span class="line">FROM information_schema.tables</span><br><span class="line">GROUP BY table_schema;</span><br><span class="line">mysql -uroot -p123 -e <span class="string">"SELECT TABLE_SCHEMA,CONCAT(SUM(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH)/1024,' KB') AS Total_KB FROM information_schema.tables GROUP BY table_schema;"</span></span><br></pre></td></tr></table></figure>



<ol>
<li>统计每个库，所有表的个数，表名</li>
</ol>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">select table_schema,count(table_name),GROUP_CONCAT(table_name) <span class="keyword">from</span> TABLES group by table_schema;</span><br></pre></td></tr></table></figure>



<ol>
<li>统计每个库占用空间大小</li>
</ol>
<figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">select table_schema,sum(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/<span class="number">1024</span> <span class="keyword">from</span> information_schema.tables group by table_schema;</span><br><span class="line"></span><br><span class="line">select table_schema,sum(DATA_LENGTH)/<span class="number">1024</span> <span class="keyword">from</span> information_schema.tables group by table_schema;</span><br></pre></td></tr></table></figure>



<ol>
<li>生成整个数据库下的所有表的单独备份语句</li>
</ol>
<figure class="highlight jsx"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">模板语句：</span><br><span class="line">mysqldump -uroot -p123 world city &gt;<span class="regexp">/tmp/</span>world_city.sql</span><br><span class="line">SELECT CONCAT(<span class="string">"mysqldump -uroot -p123 "</span>,table_schema,<span class="string">" "</span>,table_name,<span class="string">" &gt;/tmp/"</span>,table_schema,<span class="string">"_"</span>,table_name,<span class="string">".sql"</span> )</span><br><span class="line">FROM information_schema.tables</span><br><span class="line">WHERE table_schema NOT IN(<span class="string">'information_schema'</span>,<span class="string">'performance_schema'</span>,<span class="string">'sys'</span>)</span><br><span class="line">INTO OUTFILE <span class="string">'/tmp/bak.sh'</span> ;</span><br><span class="line"></span><br><span class="line">CONCAT(<span class="string">"mysqldump -uroot -p123 "</span>,table_schema,<span class="string">" "</span>,table_name,<span class="string">" &gt;/tmp/"</span>,table_schema,<span class="string">"_"</span>,table_name,<span class="string">".sql"</span> )</span><br></pre></td></tr></table></figure>

<ol>
<li>107张表，都需要执行以下2条语句</li>
</ol>
<figure class="highlight csharp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line">ALTER TABLE world.city DISCARD TABLESPACE;</span><br><span class="line">ALTER TABLE world.city IMPORT TABLESPACE;</span><br><span class="line"><span class="function">SELECT <span class="title">CONCAT</span>(<span class="params"><span class="string">"alter table "</span>,table_schema,<span class="string">"."</span>,table_name,<span class="string">" discard tablespace"</span></span>)</span></span><br><span class="line"><span class="function">FROM information_schema.tables</span></span><br><span class="line"><span class="function">WHERE table_schema</span>=<span class="string">'world'</span></span><br><span class="line">INTO OUTFILE <span class="string">'/tmp/dis.sql'</span>;</span><br></pre></td></tr></table></figure>

<h1 id="九-show-命令"><a href="#九-show-命令" class="headerlink" title="九 show 命令"></a>九 show 命令</h1><figure class="highlight python"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br></pre></td><td class="code"><pre><span class="line">show  databases;                        <span class="comment">#查看所有数据库</span></span><br><span class="line">show tables;                            <span class="comment">#查看当前库的所有表</span></span><br><span class="line">SHOW TABLES FROM                        <span class="comment">#查看某个指定库下的表</span></span><br><span class="line">show create database world              <span class="comment">#查看建库语句</span></span><br><span class="line">show create table world.city            <span class="comment">#查看建表语句</span></span><br><span class="line">show  grants <span class="keyword">for</span>  root@<span class="string">'localhost'</span>      <span class="comment">#查看用户的权限信息</span></span><br><span class="line">show  charset；                         <span class="comment">#查看字符集</span></span><br><span class="line">show collation                          <span class="comment">#查看校对规则</span></span><br><span class="line">show processlist;                       <span class="comment">#查看数据库连接情况</span></span><br><span class="line">show full processlist;                  <span class="comment">#查看数据库连接情况详细信息</span></span><br><span class="line">show privileges                         <span class="comment">#查看权限信息</span></span><br><span class="line">show index <span class="keyword">from</span>                         <span class="comment">#表的索引情况</span></span><br><span class="line">show status                             <span class="comment">#数据库状态查看</span></span><br><span class="line">SHOW STATUS LIKE <span class="string">'%lock%'</span>;              <span class="comment">#模糊查询数据库某些状态</span></span><br><span class="line">SHOW VARIABLES                          <span class="comment">#查看所有配置信息</span></span><br><span class="line">SHOW variables LIKE <span class="string">'%lock%'</span>;           <span class="comment">#查看部分配置信息</span></span><br><span class="line">show engines                            <span class="comment">#查看支持的所有的存储引擎</span></span><br><span class="line">show engine innodb status\G             <span class="comment">#查看InnoDB引擎相关的状态信息</span></span><br><span class="line">show binary logs                        <span class="comment">#列举所有的二进制日志</span></span><br><span class="line">show master status                      <span class="comment">#查看数据库的日志位置信息</span></span><br><span class="line">show binlog evnets <span class="keyword">in</span>                   <span class="comment">#查看二进制日志事件</span></span><br><span class="line">show slave status \G                    <span class="comment">#查看从库状态</span></span><br><span class="line">SHOW RELAYLOG EVENTS <span class="keyword">in</span>                 <span class="comment">#查看从库relaylog事件信息</span></span><br><span class="line">desc  (show colums <span class="keyword">from</span> city)           <span class="comment">#查看表的列定义信息</span></span><br><span class="line">http://dev.mysql.com/doc/refman/<span class="number">5.7</span>/en/show.html</span><br><span class="line">help show 查看其它的</span><br></pre></td></tr></table></figure></section>
    <!-- Tags START -->
    
    <!-- Tags END -->
    <!-- NAV START -->
    
  <div class="nav-container">
    <!-- reverse left and right to put prev and next in a more logic postition -->
    
      <a class="nav-left" href="/db/Elasticsearch%E7%B3%BB%E5%88%97/00-ElasticSearch%E4%B9%8B-%E4%BB%8B%E7%BB%8D/">
        <span class="nav-arrow">← </span>
        
          db/Elasticsearch系列/00-ElasticSearch之-介绍
        
      </a>
    
    
      <a class="nav-right" href="/db/MySQL%E7%B3%BB%E5%88%97/04-MySQL%E7%B3%BB%E5%88%97%E4%B9%8B-%E7%B4%A2%E5%BC%95%E5%8F%8A%E6%89%A7%E8%A1%8C%E8%AE%A1%E5%88%92/">
        
          db/MySQL系列/04-MySQL系列之-索引及执行计划
        
        <span class="nav-arrow"> →</span>
      </a>
    
  </div>

    <!-- NAV END -->
    <!-- 打赏 START -->
    
      <div class="money-like">
        <div class="reward-btn">
          赏
          <span class="money-code">
            <span class="alipay-code">
              <div class="code-image"></div>
              <b>使用支付宝打赏</b>
            </span>
            <span class="wechat-code">
              <div class="code-image"></div>
              <b>使用微信打赏</b>
            </span>
          </span>
        </div>
        <p class="notice">点击上方按钮,请我喝杯咖啡！</p>
      </div>
    
    <!-- 打赏 END -->
    <!-- 二维码 START -->
    
      <div class="qrcode">
        <canvas id="share-qrcode"></canvas>
        <p class="notice">扫描二维码，分享此文章</p>
      </div>
    
    <!-- 二维码 END -->
    
      <!-- Gitment START -->
      <div id="comments"></div>
      <!-- Gitment END -->
    
  </article>
  <!-- Article END -->
  <!-- Catalog START -->
  
    <aside class="catalog-container">
  <div class="toc-main">
  <!-- 不蒜子统计 -->
    <strong class="toc-title">目录</strong>
    
      <ol class="toc-nav"><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#一-SQL介绍"><span class="toc-nav-text">一 SQL介绍</span></a></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#二-常用SQL分类"><span class="toc-nav-text">二 常用SQL分类</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#2-1-客户端命令"><span class="toc-nav-text">2.1 客户端命令</span></a></li></ol></li></ol></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#三-数据类型、表属性、字符集"><span class="toc-nav-text">三 数据类型、表属性、字符集</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#3-1-数据类型"><span class="toc-nav-text">3.1  数据类型</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#3-1-1-作用"><span class="toc-nav-text">3.1.1 作用</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#3-1-2-种类"><span class="toc-nav-text">3.1.2 种类</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#3-2-表属性"><span class="toc-nav-text">3.2 表属性</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#3-2-1-列属性"><span class="toc-nav-text">3.2.1 列属性</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#3-2-2-表的属性"><span class="toc-nav-text">3.2.2 表的属性</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#3-3-字符集和校对规则"><span class="toc-nav-text">3.3 字符集和校对规则</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#3-3-1-字符集（charset）"><span class="toc-nav-text">3.3.1 字符集（charset）</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#3-3-2-校对规则（排序规则，collation）"><span class="toc-nav-text">3.3.2 校对规则（排序规则，collation）</span></a></li></ol></li></ol></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#四-DDL应用"><span class="toc-nav-text">四 DDL应用</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#4-1-数据定义语言"><span class="toc-nav-text">4.1 数据定义语言</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#4-2-库定义"><span class="toc-nav-text">4.2 库定义</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#4-2-1-创建"><span class="toc-nav-text">4.2.1 创建</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#4-2-1-创建数据库"><span class="toc-nav-text">4.2.1 创建数据库</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#4-2-2-删除-生产中禁止使用"><span class="toc-nav-text">4.2.2 删除(生产中禁止使用)</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#4-2-3-修改"><span class="toc-nav-text">4.2.3 修改</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#4-2-4-查询库相关信息（DQL）"><span class="toc-nav-text">4.2.4 查询库相关信息（DQL）</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#4-3-表定义"><span class="toc-nav-text">4.3 表定义</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#4-3-1-创建"><span class="toc-nav-text">4.3.1 创建</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#4-3-2-建表"><span class="toc-nav-text">4.3.2 建表</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#4-3-2-删除-生产中禁用命令"><span class="toc-nav-text">4.3.2 删除(生产中禁用命令)</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#4-3-3-修改"><span class="toc-nav-text">4.3.3 修改</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-4"><a class="toc-nav-link" href="#注意："><span class="toc-nav-text">注意：</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#4-3-4-表属性查询（DQL）"><span class="toc-nav-text">4.3.4 表属性查询（DQL）</span></a></li></ol></li></ol></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#五-DCL应用"><span class="toc-nav-text">五  DCL应用 ****</span></a></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#六-DML应用"><span class="toc-nav-text">六  DML应用</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#6-1-作用"><span class="toc-nav-text">6.1 作用</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#6-2-insert"><span class="toc-nav-text">6.2 insert</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#6-3-update"><span class="toc-nav-text">6.3 update</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#6-4-delete（危险！！）"><span class="toc-nav-text">6.4 delete（危险！！）</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-4"><a class="toc-nav-link" href="#全表删除"><span class="toc-nav-text">全表删除:</span></a></li><li class="toc-nav-item toc-nav-level-4"><a class="toc-nav-link" href="#伪删除："><span class="toc-nav-text">伪删除：</span></a></li></ol></li></ol></li></ol></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#七-DQL应用-select"><span class="toc-nav-text">七 DQL应用(select )</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#7-1-单独使用"><span class="toc-nav-text">7.1 单独使用</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-4"><a class="toc-nav-link" href="#默认执行顺序"><span class="toc-nav-text">默认执行顺序</span></a></li><li class="toc-nav-item toc-nav-level-4"><a class="toc-nav-link" href="#导入数据"><span class="toc-nav-text">导入数据</span></a></li></ol></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#7-2-单表子句-from"><span class="toc-nav-text">7.2 单表子句-from</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#7-3-单表子句-where"><span class="toc-nav-text">7.3 单表子句-where</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#7-3-1-where配合等值查询"><span class="toc-nav-text">7.3.1 where配合等值查询</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#7-3-2-where配合比较操作符-gt-lt-gt-lt-lt-gt"><span class="toc-nav-text">7.3.2 where配合比较操作符(&gt; &lt; &gt;&#x3D; &lt;&#x3D; &lt;&gt;)</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#7-3-3-where配合逻辑运算符-and-or"><span class="toc-nav-text">7.3.3 where配合逻辑运算符(and  or )</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#7-3-4-where配合模糊查询"><span class="toc-nav-text">7.3.4 where配合模糊查询</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#7-3-5-where配合in语句"><span class="toc-nav-text">7.3.5 where配合in语句</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#7-3-6-where配合between-and"><span class="toc-nav-text">7.3.6 where配合between and</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#7-4-group-by-常用聚合函数"><span class="toc-nav-text">7.4 group by + 常用聚合函数</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#7-4-1-作用"><span class="toc-nav-text">7.4.1 作用</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#7-4-2-常用聚合函数"><span class="toc-nav-text">7.4.2 常用聚合函数</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#7-4-3-例子："><span class="toc-nav-text">7.4.3 例子：</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#7-5-having"><span class="toc-nav-text">7.5 having</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#7-6-order-by-limit"><span class="toc-nav-text">7.6 order by + limit</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#7-6-1-作用"><span class="toc-nav-text">7.6.1 作用</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#7-6-2-应用案例"><span class="toc-nav-text">7.6.2 应用案例</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#7-7-distinct：去重复"><span class="toc-nav-text">7.7 distinct：去重复</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#7-8-联合查询-union-all"><span class="toc-nav-text">7.8 联合查询- union all</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#7-9-join-多表连接查询"><span class="toc-nav-text">7.9 join 多表连接查询</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#7-9-0-案例准备"><span class="toc-nav-text">7.9.0 案例准备</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#7-9-1-语法"><span class="toc-nav-text">7.9.1 语法</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#7-9-2-例子："><span class="toc-nav-text">7.9.2 例子：</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#7-9-3-别名"><span class="toc-nav-text">7.9.3 别名</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#7-9-4-多表SQL练习题"><span class="toc-nav-text">7.9.4 多表SQL练习题</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#注意：-1"><span class="toc-nav-text">注意：</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#7-9-5-综合练习"><span class="toc-nav-text">7.9.5 综合练习</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#7-9-6-补充"><span class="toc-nav-text">7.9.6 补充</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-4"><a class="toc-nav-link" href="#别名"><span class="toc-nav-text">别名</span></a></li></ol></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#"><span class="toc-nav-text"></span></a></li></ol></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#八-元数据信息"><span class="toc-nav-text">八 元数据信息</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#8-1-逻辑表有关组成部分"><span class="toc-nav-text">8.1 逻辑表有关组成部分</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#8-2-information-schema-tables视图"><span class="toc-nav-text">8.2 information_schema.tables视图</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#九-show-命令"><span class="toc-nav-text">九 show 命令</span></a></li></ol>
    
  </div>
</aside>
  
  <!-- Catalog END -->
</main>

<script>
  (function () {
    var url = 'http://www.liuqingzheng.top/db/MySQL系列/03-MySQL系列之-SQL基础应用/';
    var banner = ''
    if (banner !== '' && banner !== 'undefined' && banner !== 'null') {
      $('#article-banner').css({
        'background-image': 'url(' + banner + ')'
      })
    } else {
      $('#article-banner').geopattern(url)
    }
    $('.header').removeClass('fixed-header')

    // error image
    $(".markdown-content img").on('error', function() {
      $(this).attr('src', 'http://file.muyutech.com/error-img.png')
      $(this).css({
        'cursor': 'default'
      })
    })

    // zoom image
    $(".markdown-content img").on('click', function() {
      var src = $(this).attr('src')
      if (src !== 'http://file.muyutech.com/error-img.png') {
        var imageW = $(this).width()
        var imageH = $(this).height()

        var zoom = ($(window).width() * 0.95 / imageW).toFixed(2)
        zoom = zoom < 1 ? 1 : zoom
        zoom = zoom > 2 ? 2 : zoom
        var transY = (($(window).height() - imageH) / 2).toFixed(2)

        $('body').append('<div class="image-view-wrap"><div class="image-view-inner"><img src="'+ src +'" /></div></div>')
        $('.image-view-wrap').addClass('wrap-active')
        $('.image-view-wrap img').css({
          'width': `${imageW}`,
          'transform': `translate3d(0, ${transY}px, 0) scale3d(${zoom}, ${zoom}, 1)`
        })
        $('html').css('overflow', 'hidden')

        $('.image-view-wrap').on('click', function() {
          $(this).remove()
          $('html').attr('style', '')
        })
      }
    })
  })();
</script>


  <script>
    var qr = new QRious({
      element: document.getElementById('share-qrcode'),
      value: document.location.href
    });
  </script>



  <script>
    var gitmentConfig = "liuqingzheng";
    if (gitmentConfig !== 'undefined') {
      var gitment = new Gitment({
        id: "db/MySQL系列/03-MySQL系列之-SQL基础应用",
        owner: "liuqingzheng",
        repo: "FuckBlog",
        oauth: {
          client_id: "32a4076431cf39d0ecea",
          client_secret: "94484bd79b3346a949acb2fda3c8a76ce16990c6"
        },
        theme: {
          render(state, instance) {
            const container = document.createElement('div')
            container.lang = "en-US"
            container.className = 'gitment-container gitment-root-container'
            container.appendChild(instance.renderHeader(state, instance))
            container.appendChild(instance.renderEditor(state, instance))
            container.appendChild(instance.renderComments(state, instance))
            container.appendChild(instance.renderFooter(state, instance))
            return container;
          }
        }
      })
      gitment.render(document.getElementById('comments'))
    }
  </script>




    <div class="scroll-top">
  <span class="arrow-icon"></span>
</div>
    <footer class="app-footer">
<!-- 不蒜子统计 -->
<span id="busuanzi_container_site_pv">
     本站总访问量<span id="busuanzi_value_site_pv"></span>次
</span>
<span class="post-meta-divider">|</span>
<span id="busuanzi_container_site_uv" style='display:none'>
     本站访客数<span id="busuanzi_value_site_uv"></span>人
</span>
<script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>



  <p class="copyright">
    &copy; 2021 | Proudly powered by <a href="https://www.cnblogs.com/xiaoyuanqujing" target="_blank">小猿取经</a>
    <br>
    Theme by <a href="https://www.cnblogs.com/xiaoyuanqujing" target="_blank" rel="noopener">小猿取经</a>
  </p>
</footer>

<script>
  function async(u, c) {
    var d = document, t = 'script',
      o = d.createElement(t),
      s = d.getElementsByTagName(t)[0];
    o.src = u;
    if (c) { o.addEventListener('load', function (e) { c(null, e); }, false); }
    s.parentNode.insertBefore(o, s);
  }
</script>
<script>
  async("//cdnjs.cloudflare.com/ajax/libs/fastclick/1.0.6/fastclick.min.js", function(){
    FastClick.attach(document.body);
  })
</script>

<script>
  var hasLine = 'true';
  async("//cdnjs.cloudflare.com/ajax/libs/highlight.js/9.12.0/highlight.min.js", function(){
    $('figure pre').each(function(i, block) {
      var figure = $(this).parents('figure');
      if (hasLine === 'false') {
        figure.find('.gutter').hide();
      }
      var lang = figure.attr('class').split(' ')[1] || 'code';
      var codeHtml = $(this).html();
      var codeTag = document.createElement('code');
      codeTag.className = lang;
      codeTag.innerHTML = codeHtml;
      $(this).attr('class', '').empty().html(codeTag);
      figure.attr('data-lang', lang.toUpperCase());
      hljs.highlightBlock(block);
    });
  })
</script>





<!-- Baidu Tongji -->

<script>
    var _baId = 'c5fd96eee1193585be191f318c3fa725';
    // Originial
    var _hmt = _hmt || [];
    (function() {
      var hm = document.createElement("script");
      hm.src = "//hm.baidu.com/hm.js?" + _baId;
      var s = document.getElementsByTagName("script")[0];
      s.parentNode.insertBefore(hm, s);
    })();
</script>


<script src="/js/script.js"></script>


<script src="/js/search.js"></script>


<script src="/js/load.js"></script>



  <span class="local-search local-search-google local-search-plugin" style="right: 50px;top: 70px;;position:absolute;z-index:2;">
      <input type="search" placeholder="站内搜索" id="local-search-input" class="local-search-input-cls" style="">
      <div id="local-search-result" class="local-search-result-cls"></div>
  </span>


  </body>
</html>